"""
    本文件用于V2.0版本的用例类型的统计查询SQL
"""


def construct_person_system_subsys_case_execute_statistics(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _case_type,
        _page_No,
        _page_size
):
    return f"""
select
    pject.id as project_id,
    pject.name as project_name,
    pj.id as subsys_id,
    pj.name as subsys_name,
    usr.realname as tester,
    count(1) as total,
    count(if(cas.type = 'feature', 1, null)) as feature,
    count(if(cas.type = 'SFYZ', 1, null)) as SFYZ,
    count(if(cas.type = 'XTXN', 1, null)) as XTXN,
    count(if(cas.type = 'DTYZ', 1, null)) as DTYZ,
    count(if(cas.type = 'PTYZ', 1, null)) as PTYZ,
    count(if(cas.type = 'performance', 1, null)) as performance,
    count(if(cas.type = 'config', 1, null)) as config,
    count(if(cas.type = 'install', 1, null)) as install,
    count(if(cas.type = 'security', 1, null)) as security,
    count(if(cas.type = 'interface', 1, null)) as interface,
    count(if(cas.type = 'unit', 1, null)) as unit,
    count(if(cas.type = 'other', 1, null)) as other
from
    zt_testresult as trest
left join
    zt_testrun as trun
on
    trun.id = trest.run
left join
    zt_case as cas
on
    cas.id=trun.`case`
LEFT JOIN
    zt_testtask as tsk 
on 
    tsk.id=trun.task 
LEFT JOIN
    zt_project as pj 
ON
    pj.id=tsk.project
LEFT JOIN
    zt_project as pject 
ON
    SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
left join
    zt_user as usr
on
    usr.account = trun.lastRunner
where
    trest.run > 0
and
    cas.deleted = '0'
-- and
--     pject.status != 'closed'
{
    f'''AND
            pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1 
    else f'''
        AND
            pject.id = {_project_ids[0]}
    ''' if _project_ids
    else
        ''
}
{
    f'''AND
            usr.realname in  {_testers}''' if _testers and len(_testers) > 1 
    else f'''
        AND
            usr.realname = "{_testers[0]}"
    ''' if _testers
    else 
        ''
}
{
    '''AND
	        trun.lastRunDate >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        trun.lastRunDate < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            cas.type in {_case_type}''' if _case_type and len(_case_type) > 1
    else f'''
        And 
            cas.type = "{_case_type[0]}"
    ''' if _case_type
    else 
        ''
}
group by
    trun.lastRunner, pj.id
order by
        trun.lastRunner
"""



def construct_person_system_subsys_case_create_statistics(
        _testers,
        _project_ids,
        _date_start_time,
        _date_end_time,
        _case_type,
        _page_No,
        _page_size
):
    return f"""
select
            if(pd.program=0, pd.id, pject.id)               as project_id,
            if(pd.program=0, pd.name, pject.name)           as project_name,
            if(cas.project>0, pj.id, pd.id)                 as subsys_id,
            if(cas.project>0, pj.name, pd.name)             as subsys_name,
            usr.realname                                    as tester,
            count(cas.id)                                   as total,
            count(if(cas.type = 'feature', 1, null))        as feature,
            count(if(cas.type = 'SFYZ', 1, null))           as SFYZ,
            count(if(cas.type = 'XTXN', 1, null))           as XTXN,
            count(if(cas.type = 'DTYZ', 1, null))           as DTYZ,
            count(if(cas.type = 'PTYZ', 1, null))           as PTYZ,
            count(if(cas.type = 'performance', 1, null))    as performance,
            count(if(cas.type = 'config', 1, null))         as config,
            count(if(cas.type = 'install', 1, null))        as install,
            count(if(cas.type = 'security', 1, null))       as security,
            count(if(cas.type = 'interface', 1, null))      as interface,
            count(if(cas.type = 'unit', 1, null))           as unit,
            count(if(cas.type = 'other', 1, null))          as other
from 
	        zt_case as cas
left join
			zt_user as usr
on
			usr.account = cas.openedBy
LEFT JOIN
			zt_project as pj 
ON
			pj.id = cas.project 
LEFT JOIN
			zt_product as pd 
on 
			pd.id = cas.product
LEFT JOIN
			zt_project as pject 
on 
			pject.id=if(cas.project=0, pd.program, SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1))
where 
			cas.deleted = '0'
and 
			cas.fromCaseId = 0
{
    f'''AND
            pject.id in  {_project_ids}''' if _project_ids and len(_project_ids) > 1 
    else f'''
        AND
            pject.id = {_project_ids[0]}
    ''' if _project_ids
    else
        ''
}
{
    f'''AND
            usr.realname in  {_testers}''' if _testers and len(_testers) > 1 
    else f'''
        AND
            usr.realname = "{_testers[0]}"
    ''' if _testers
    else 
        ''
}
{
    '''AND
	        cas.openedDate >= '%s' ''' % _date_start_time if _date_start_time else ''
}
{
    '''AND
	        cas.openedDate < '%s' ''' % _date_end_time if _date_end_time else ''
}
{
    f'''AND
            cas.type in {_case_type}''' if _case_type and len(_case_type) > 1
    else f'''
        And 
            cas.type = "{_case_type[0]}"
    ''' if _case_type
    else 
        ''
}
group by 
					cas.openedBy, pd.id
order by
						cas.openedBy
"""



testcase_assigner_name_list_sql = """
SELECT
		usr.realname as name
FROM
		zentao.zt_case as cas
LEFT JOIN
		zentao.zt_user as usr
ON
		usr.account=cas.openedBy
WHERE
		usr.deleted='0'
AND
        usr.realname != 'admin'
GROUP BY
		cas.openedBy
"""

testcase_id_list_sql = """
select 
    cas.id as case_id
from 
    zentao.zt_case as cas 
where
    cas.deleted = '0'
"""


def construct_sql_case_statistics_detail(
        _project_id,
        _subsys_id,
        _iteration_id,
        _version_id,
        _test_sheet_id,
        _case_id,
        _case_type,
        _assigner_name,
        _executor_name,
        _creator_name,
        _date_create_start,
        _date_create_end,
        _execute_result,
        _date_execute_start,
        _date_execute_end,
        _pageNo=0,
        _pageNum=25):
    return f"""
# 用例统计详情
select 
            pject.id as project_id, 
            pj.id as subsys_id, 
            pject.name as pject_name,
            pj.name as subsys_name,
            tsk.id as task_id,
            tsk.name as task_name,
            iter.id as iteration_id,
            iter.name as iteration_name,
            bld.id as version_id,
            bld.name as version_name,
            cas.id as case_id,
            cas.pri as priority,
            cas.title as title,
            cas.status as status,
            cas.type as case_type,
            usr.realname as creator,
            usrB.realname as executor,
            cas.openedDate as create_date,
--             trun.assignedTo as assigned_to,
            usrC.realname as assigned_to,
            trun.lastRunDate as execute_date,
            trun.lastRunResult as last_run_result,
            count(distinct bug.id) as bug_num,
            count(1) as execute_num
FROM
			zt_testresult as trest 
LEFT JOIN
			zt_testrun as trun 
ON
			trun.id = trest.run
LEFT JOIN
			zt_testtask as tsk 
on 
			tsk.id=trun.task 
LEFT JOIN
			zt_case as cas 
ON
			cas.id=trun.`case`
LEFT JOIN
			zt_project as pj 
ON
			pj.id=tsk.project
LEFT JOIN
			zt_project as pject 
ON
			SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
LEFT JOIN
			zt_user as usr 
ON
			usr.account=cas.openedBy
LEFT JOIN
			zt_user as usrC 
ON
			usrC.account=trun.assignedTo
LEFT JOIN
			zt_user as usrB 
ON
			usrB.account=trun.lastRunner
left join 
            zt_bug as bug
on
            bug.id = trest.`case`
left join  
            zt_project as iter
on 
            tsk.execution = iter.id
left join  
            zt_build as bld 
on  
            bld.execution = iter.id
WHERE
			trest.run > 0
AND
			cas.deleted = '0'
AND
			pj.deleted = '0'
AND
			tsk.deleted = '0'	
{
    '''AND
            usr.realname = '%s' # 创建人''' % _creator_name if _creator_name else ''
    }
{
    '''AND
            pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id > 0 else
    '''AND
        pj.id = %d # 项目id''' if _project_id == 0
    else ''
    }
{
    '''AND
            pj.id = %d # 子系统id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
    }
{
    '''AND
            iter.id = %d # 迭代id''' % _iteration_id if type(_iteration_id) is int and _iteration_id > 0 else ''
    }
{
    '''AND
            bld.id = %d # 版本id''' % _version_id if type(_version_id) is int and _version_id > 0 else ''
    }
{
    '''AND
            tsk.id = %d # 测试单id''' % _test_sheet_id if type(_test_sheet_id) is int and _test_sheet_id > 0 else ''
    }
{
    '''AND
            cas.id = %d # 测试用例id''' % _case_id if type(_case_id) is int and _case_id > 0 else ''
    }
{
    '''AND
            usrB.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
    }
{
    '''AND
            usrC.realname = '%s' # 指派人''' % _assigner_name if _assigner_name else ''
    }
{
    '''AND
            trun.lastRunResult = '%s' # 用例执行结果''' % _execute_result if _execute_result else ''
    }
{
    '''AND
            cas.type = '%s' # 用例类型''' % _case_type if _case_type else ''
    }
{
    f'''
		{"AND trun.lastRunDate >= '%s' # 执行时间" % _date_execute_start if _date_execute_start else ''} 
		{"AND trun.lastRunDate <= '%s' # 执行时间" % _date_execute_end if _date_execute_end else ''}'''
    }
{
    f'''
		{"AND cas.openedDate >= '%s' # 创建时间" % _date_create_start if _date_create_start else ''} 
		{"AND cas.openedDate <= '%s' # 创建时间" % _date_create_end if _date_create_end else ''}'''
    }
GROUP BY
			trest.case, trest.run, trest.lastRunner
order by 
                trun.lastRunDate
desc
limit {_pageNo * _pageNum}, {_pageNum}
"""


# 统计所有的用例执行情况
def construct_sql_case_statistics_all_sql_detail(
        _project_id,
        _subsys_id,
        _iteration_id,
        _version_id,
        _test_sheet_id,
        _case_id,
        _case_type,
        _assigner_name,
        _executor_name,
        _creator_name,
        _date_create_start,
        _date_create_end,
        _execute_result,
        _date_execute_start,
        _date_execute_end
):
    return f"""
# 执行统计聚合结果
select 
            count(id) as total
from
    (select 	
                trest.id as id 
    FROM
			zt_testresult as trest 
    LEFT JOIN
                zt_testrun as trun 
    ON
                trun.id = trest.run
    LEFT JOIN
                zt_testtask as tsk 
    on 
                tsk.id=trun.task 
    LEFT JOIN
                zt_case as cas 
    ON
                cas.id=trun.`case`
    LEFT JOIN
                zt_project as pj 
    ON
                pj.id=tsk.project
    LEFT JOIN
                zt_project as pject 
    ON
                SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    LEFT JOIN
                zt_user as usr 
    ON
                usr.account=cas.openedBy
    LEFT JOIN
                zt_user as usrC 
    ON
                usrC.account=trun.assignedTo
    LEFT JOIN
                zt_user as usrB 
    ON
                usrB.account=trun.lastRunner
    left join 
                zt_bug as bug
    on
                bug.id = trest.`case`
    left join  
                zt_project as iter
    on 
                tsk.execution = iter.id
    left join  
                zt_build as bld 
    on  
                bld.execution = iter.id
    WHERE
                trest.run > 0
    AND
                cas.deleted = '0'
    AND
                pj.deleted = '0'
    AND
                tsk.deleted = '0'	
    {
    '''AND
            usr.realname = '%s' # 创建人''' % _creator_name if _creator_name else ''
    }
    {
        '''AND
                pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id > 0 else
        '''AND
            pj.id = %d # 项目id''' if _project_id == 0
        else ''
        }
    {
        '''AND
                pj.id = %d # 子系统id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
        }
    {
        '''AND
                iter.id = %d # 迭代id''' % _iteration_id if type(_iteration_id) is int and _iteration_id > 0 else ''
        }
    {
        '''AND
                bld.id = %d # 版本id''' % _version_id if type(_version_id) is int and _version_id > 0 else ''
        }
    {
        '''AND
                tsk.id = %d # 测试单id''' % _test_sheet_id if type(_test_sheet_id) is int and _test_sheet_id > 0 else ''
        }
    {
        '''AND
                cas.id = %d # 测试用例id''' % _case_id if type(_case_id) is int and _case_id > 0 else ''
        }
    {
        '''AND
                usrB.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
        }
    {
        '''AND
                usrC.realname = '%s' # 指派人''' % _assigner_name if _assigner_name else ''
        }
    {
        '''AND
                trun.lastRunResult = '%s' # 用例执行结果''' % _execute_result if _execute_result else ''
        }
    {
        '''AND
                cas.type = '%s' # 用例类型''' % _case_type if _case_type else ''
        }
    {
        f'''
            {"AND trun.lastRunDate >= '%s' # 执行时间" % _date_execute_start if _date_execute_start else ''} 
            {"AND trun.lastRunDate <= '%s' # 执行时间" % _date_execute_end if _date_execute_end else ''}'''
        }
    {
        f'''
            {"AND cas.openedDate >= '%s' # 创建时间" % _date_create_start if _date_create_start else ''} 
            {"AND cas.openedDate <= '%s' # 创建时间" % _date_create_end if _date_create_end else ''}'''
        }
    GROUP BY
                trest.case, trest.run, trest.lastRunner
    ) as new
"""



def construct_sql_case_create_statistics_detail(
        _project_id,
        _subsys_id,
        _iteration_id,
        _version_id,
        _module_id,
        _case_id,
        _case_type,
        _story_id,
        _case_from,
        _creator_name,
        _date_create_start,
        _date_create_end,
        _pageNo=0,
        _pageNum=25):
    return f"""
# 用例创建详情
select 
            pject.id as project_id, 
            pject.name as pject_name,
            pj.id as subsys_id, 
            pj.name as subsys_name,
            iter.id as iteration_id,
            iter.name as iteration_name,
            bld.id as version_id,
            bld.name as version_name,
            md.id as module_id,
            md.name as module_name,
            cas.id as case_id,
            cas.pri as priority,
            cas.title as title,
            cas.type as case_type,
            sty.id as story_id,
            sty.title as story_title,
            cas.fromCaseId as case_from,
            usr.realname as creator,
            cas.openedDate as create_date
FROM
			zt_case as cas 
left join   
            zt_user as usr
on 
            usr.account = cas.openedBy
LEFT JOIN
			zt_project as pj
ON
			pj.id=cas.project
LEFT JOIN
			zt_project as pject 
ON
			SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
LEFT JOIN
			zt_module as md 
ON
            md.id = cas.module
left join 
            zt_story as sty 
on 
            sty.id = cas.story
left join  
            zt_project as iter
on 
            cas.execution = iter.id
left join  
            zt_build as bld 
on
            bld.execution = iter.id
WHERE
			cas.deleted = '0'
AND
			pj.deleted = '0'
and
            cas.fromCaseId = 0
{
    '''AND
            usr.realname = '%s' # 创建人''' % _creator_name if _creator_name else ''
    }
{
    '''AND
            pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id > 0 else ''
    '''AND
        pj.id = %d # 项目id''' if _project_id == 0
    else ''
    }
{
    '''AND
            pj.id = %d # 子系统id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
    }
{
    '''AND
            iter.id = %d # 迭代id''' % _iteration_id if type(_iteration_id) is int and _iteration_id > 0 else ''
    }
{
    '''AND
            bld.id = %d # 版本id''' % _version_id if type(_version_id) is int and _version_id > 0 else ''
    }
{
    '''AND
            cas.id = %d # 测试用例id''' % _case_id if type(_case_id) is int and _case_id > 0 else ''
    }
{
    '''AND
            md.id = %d # 所属模块id''' % _module_id if type(_module_id) is int and _module_id > 0 else ''
    }
{
    '''AND
            sty.id = %d # 需求id''' % _story_id if type(_story_id) is int and _story_id > 0 else ''
    }
{
    '''AND
            cas.fromCaseId = '%s' # 用例状态''' % _case_from if type(_case_from) is int and _case_from > 0 else ''
    }
{
    '''AND
            cas.type = '%s' # 用例类型''' % _case_type if _case_type else ''
    }
{
    f'''
		{"AND cas.openedDate >= '%s' # 创建的时间" % _date_create_start if _date_create_start else ''} 
		{"AND cas.openedDate <= '%s' # 创建的时间" % _date_create_end if _date_create_end else ''}'''
    }
limit {_pageNo * _pageNum}, {_pageNum}
"""

# 统计所有的用例执行情况
def construct_sql_case_create_statistics_all_sql_detail(
        _project_id,
        _subsys_id,
        _iteration_id,
        _version_id,
        _module_id,
        _case_id,
        _case_type,
        _story_id,
        _case_from,
        _creator_name,
        _date_create_start,
        _date_create_end
):
    return f"""
# 创建用例总数统计
select 
            count(id) as total
from
    (select 
            cas.id as id 
FROM
			zt_case as cas 
left join   
            zt_user as usr
on 
            usr.account = cas.openedBy
LEFT JOIN
			zt_project as pj
ON
			pj.id=cas.project
LEFT JOIN
			zt_project as pject 
ON
			SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
LEFT JOIN
			zt_module as md 
ON
            md.id = cas.module
left join 
            zt_story as sty 
on 
            sty.id = cas.story
left join  
            zt_project as iter
on 
            cas.execution = iter.id
left join  
            zt_build as bld 
on
            bld.execution = iter.id
WHERE
			cas.deleted = '0'
AND
			pj.deleted = '0'
and 
			cas.fromCaseId = 0
{
    '''AND
            usr.realname = '%s' # 创建人''' % _creator_name if _creator_name else ''
    }
{
    '''AND
            pject.id = %d # 项目id''' % _project_id if type(_project_id) is int and _project_id > 0 else ''
    '''AND
        pj.id = %d # 项目id''' if _project_id == 0
    else ''
    }
{
    '''AND
            pj.id = %d # 子系统id''' % _subsys_id if type(_subsys_id) is int and _subsys_id > 0 else ''
    }
{
    '''AND
            iter.id = %d # 迭代id''' % _iteration_id if type(_iteration_id) is int and _iteration_id > 0 else ''
    }
{
    '''AND
            bld.id = %d # 版本id''' % _version_id if type(_version_id) is int and _version_id > 0 else ''
    }
{
    '''AND
            cas.id = %d # 测试用例id''' % _case_id if type(_case_id) is int and _case_id > 0 else ''
    }
{
    '''AND
            md.id = %d # 所属模块id''' % _module_id if type(_module_id) is int and _module_id > 0 else ''
    }
{
    '''AND
            sty.id = %d # 需求id''' % _story_id if type(_story_id) is int and _story_id > 0 else ''
    }
{
    '''AND
            cas.fromCaseId = '%s' # 用例状态''' % _case_from if type(_case_from) is int and _case_from > 0 else ''
    }
{
    '''AND
            cas.type = '%s' # 用例类型''' % _case_type if _case_type else ''
    }
{
    f'''
		{"AND cas.openedDate >= '%s' # 创建的时间" % _date_create_start if _date_create_start else ''} 
		{"AND cas.openedDate <= '%s' # 创建的时间" % _date_create_end if _date_create_end else ''}'''
    }
    ) as new
"""



case_tester_name_list_sql = """
SELECT
		usr.realname as name
FROM
		zentao.zt_case as cas
LEFT JOIN
		zentao.zt_user as usr
ON
		usr.account=cas.openedBy
WHERE
		usr.deleted='0'
AND
        usr.realname != 'admin'
GROUP BY
		cas.openedBy
"""


def construct_sql_case_execute_data_distribute_by_time_project_slot_with_each(
        _date_execute_start,
        _date_execute_end,
        _executor_name,
        _case_type
):
    return f"""
    select
        usr.realname as executor,
        pject.id     as project_id,
        pject.name   as project_name, 
        count(if(TIME(trun.lastRunDate) >='8:00'  and TIME(trun.lastRunDate) <  '9:00', 1, null)) as '+8',
        count(if(TIME(trun.lastRunDate) >='9 :00' and TIME(trun.lastRunDate) < '10:00', 1, null)) as '+9',
        count(if(TIME(trun.lastRunDate) >='10:00' and TIME(trun.lastRunDate) < '11:00', 1, null)) as '+10',
        count(if(TIME(trun.lastRunDate) >='11:00' and TIME(trun.lastRunDate) < '12:00', 1, null)) as '+11',
        count(if(TIME(trun.lastRunDate) >='12:00' and TIME(trun.lastRunDate) < '13:30', 1, null)) as '+12',
        count(if(TIME(trun.lastRunDate) >='13:30' and TIME(trun.lastRunDate) < '14:30', 1, null)) as '+13',
        count(if(TIME(trun.lastRunDate) >='14:30' and TIME(trun.lastRunDate) < '15:30', 1, null)) as '+14',
        count(if(TIME(trun.lastRunDate) >='15:30' and TIME(trun.lastRunDate) < '16:30', 1, null)) as '+15',
        count(if(TIME(trun.lastRunDate) >='16:30' and TIME(trun.lastRunDate) < '17:30', 1, null)) as '+16',
        count(if(TIME(trun.lastRunDate) >='17:30' and TIME(trun.lastRunDate) < '18:30', 1, null)) as '+17',
        count(if(TIME(trun.lastRunDate) >='18:30' and TIME(trun.lastRunDate) < '19:30', 1, null)) as '+18',
        count(if(TIME(trun.lastRunDate) >='19:30' and TIME(trun.lastRunDate) < '20:30', 1, null)) as '+19',
        count(if(TIME(trun.lastRunDate) >='20:30' and TIME(trun.lastRunDate) < '21:30', 1, null)) as '+20',
        count(if(TIME(trun.lastRunDate) >='21:30' and TIME(trun.lastRunDate) < '22:30', 1, null)) as '+21',
        count(if(TIME(trun.lastRunDate) >='22:30' and TIME(trun.lastRunDate) <  '8:00', 1, null)) as '+22',
        count(1) as total
    from
        zt_testresult as trest
    left join
        zt_testrun as trun
    on
        trun.id = trest.run
    left join
        zt_case as cas
    on
        cas.id=trun.`case`
    LEFT JOIN
        zt_testtask as tsk 
    on 
        tsk.id=trun.task 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=tsk.project
    LEFT JOIN
        zt_project as pject 
    ON
        SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    left join
        zt_user as usr
    on
        usr.account = trun.lastRunner
    where
        trest.run > 0
    and
        cas.deleted = '0'
    and 
        pject.status != 'closed'   
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
    {
    '''AND
        cas.type = "%s" # 用例类型''' % _case_type[0] if _case_type and len(_case_type) == 1 
    else '''
       AND
        cas.type in %s ''' % str(_case_type) if _case_type and len(_case_type) > 1
    else ''
    }
    {
    '''AND
        usr.realname = '%s' # 执行人''' % _executor_name[0] if _executor_name and len(_executor_name) == 1 
    else '''
       AND
        usr.realname in %s ''' % str(_executor_name) if _executor_name and len(_executor_name) > 1
    else ''
    }
    group by
        trun.lastRunner, pject.id
    order by
        trun.lastRunner
    desc
"""



def construct_sql_case_execute_data_distribute_by_date_project_with_each(
    _date_execute_start,
    _date_execute_end,
    _executor_name,
    _case_type
):
    return f"""
select
    usr.realname as executor,
    pject.id     as project_id,
    pject.name   as project_name,
    count(if(Day(trun.lastRunDate) >= 1  and Day(trun.lastRunDate) < 2, 1 , null)) as "1",
    count(if(Day(trun.lastRunDate) >= 2  and Day(trun.lastRunDate) < 3, 1 , null)) as "2",
    count(if(Day(trun.lastRunDate) >= 3  and Day(trun.lastRunDate) < 4, 1 , null)) as "3",
    count(if(Day(trun.lastRunDate) >= 4  and Day(trun.lastRunDate) < 5, 1 , null)) as "4",
    count(if(Day(trun.lastRunDate) >= 5  and Day(trun.lastRunDate) < 6, 1 , null)) as "5",
    count(if(Day(trun.lastRunDate) >= 6  and Day(trun.lastRunDate) < 7, 1 , null)) as "6",
    count(if(Day(trun.lastRunDate) >= 7  and Day(trun.lastRunDate) < 8, 1 , null)) as "7",
    count(if(Day(trun.lastRunDate) >= 8  and Day(trun.lastRunDate) < 9, 1 , null)) as "8",
    count(if(Day(trun.lastRunDate) >= 9  and Day(trun.lastRunDate) < 10, 1 , null)) as "9",
    count(if(Day(trun.lastRunDate) >= 10 and Day(trun.lastRunDate) < 11, 1 , null)) as "10",
    count(if(Day(trun.lastRunDate) >= 11 and Day(trun.lastRunDate) < 12, 1 , null)) as "11",
    count(if(Day(trun.lastRunDate) >= 12 and Day(trun.lastRunDate) < 13, 1 , null)) as "12",
    count(if(Day(trun.lastRunDate) >= 13 and Day(trun.lastRunDate) < 14, 1 , null)) as "13",
    count(if(Day(trun.lastRunDate) >= 14 and Day(trun.lastRunDate) < 15, 1 , null)) as "14",
    count(if(Day(trun.lastRunDate) >= 15 and Day(trun.lastRunDate) < 16, 1 , null)) as "15",
    count(if(Day(trun.lastRunDate) >= 16 and Day(trun.lastRunDate) < 17, 1 , null)) as "16",
    count(if(Day(trun.lastRunDate) >= 17 and Day(trun.lastRunDate) < 18, 1 , null)) as "17",
    count(if(Day(trun.lastRunDate) >= 18 and Day(trun.lastRunDate) < 19, 1 , null)) as "18",
    count(if(Day(trun.lastRunDate) >= 19 and Day(trun.lastRunDate) < 20, 1 , null)) as "19",
    count(if(Day(trun.lastRunDate) >= 20 and Day(trun.lastRunDate) < 21, 1 , null)) as "20",
    count(if(Day(trun.lastRunDate) >= 21 and Day(trun.lastRunDate) < 22, 1 , null)) as "21",
    count(if(Day(trun.lastRunDate) >= 22 and Day(trun.lastRunDate) < 23, 1 , null)) as "22",
    count(if(Day(trun.lastRunDate) >= 23 and Day(trun.lastRunDate) < 24, 1 , null)) as "23",
    count(if(Day(trun.lastRunDate) >= 24 and Day(trun.lastRunDate) < 25, 1 , null)) as "24",
    count(if(Day(trun.lastRunDate) >= 25 and Day(trun.lastRunDate) < 26, 1 , null)) as "25",
    count(if(Day(trun.lastRunDate) >= 26 and Day(trun.lastRunDate) < 27, 1 , null)) as "26",
    count(if(Day(trun.lastRunDate) >= 27 and Day(trun.lastRunDate) < 28, 1 , null)) as "27",
    count(if(Day(trun.lastRunDate) >= 28 and Day(trun.lastRunDate) < 29, 1 , null)) as "28",
    count(if(Day(trun.lastRunDate) >= 29 and Day(trun.lastRunDate) < 30, 1 , null)) as "29",
    count(if(Day(trun.lastRunDate) >= 30 and Day(trun.lastRunDate) < 31, 1 , null)) as "30",
    count(if(Day(trun.lastRunDate) = 31, 1 , null)) as "31",
    count(1) as total

from
    zt_testresult as trest
left join
    zt_testrun as trun
on
    trun.id = trest.run
left join
    zt_case as cas
on
    cas.id=trun.`case`
LEFT JOIN
    zt_testtask as tsk 
on 
    tsk.id=trun.task 
LEFT JOIN
    zt_project as pj 
ON
    pj.id=tsk.project
LEFT JOIN
    zt_project as pject 
ON
    SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
left join
    zt_user as usr
on
    usr.account = trun.lastRunner
where
    trest.run > 0
and
    cas.deleted = '0'
# and
#     pject.status != 'closed'
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
    {
    '''AND
        cas.type = "%s" # 用例类型''' % _case_type[0] if _case_type and len(_case_type) == 1 
    else '''
       AND
        cas.type in %s ''' % str(_case_type) if _case_type and len(_case_type) > 1
    else ''
    }
    {
    '''AND
        usr.realname = '%s' # 执行人''' % _executor_name[0] if _executor_name and len(_executor_name) == 1
    else '''
       AND
        usr.realname in %s ''' % str(_executor_name) if _executor_name and len(_executor_name) > 1
    else ''
    }
group by
    trun.lastRunner, pject.id
order by
        trun.lastRunner
    desc
"""



def construct_sql_case_create_data_distribute_by_date_project_with_each(
    _date_create_start,
    _date_create_end,
    _creator_name
):
    return f"""
    select
            usr.realname                                    as creator,
            pject.id                                        as project_id,
            pject.name                                      as project_name,
            count(if(Day(cas.openedDate) >= 1  and Day(cas.openedDate) < 2, 1 , null)) as "1",
            count(if(Day(cas.openedDate) >= 2  and Day(cas.openedDate) < 3, 1 , null)) as "2",
            count(if(Day(cas.openedDate) >= 3  and Day(cas.openedDate) < 4, 1 , null)) as "3",
            count(if(Day(cas.openedDate) >= 4  and Day(cas.openedDate) < 5, 1 , null)) as "4",
            count(if(Day(cas.openedDate) >= 5  and Day(cas.openedDate) < 6, 1 , null)) as "5",
            count(if(Day(cas.openedDate) >= 6  and Day(cas.openedDate) < 7, 1 , null)) as "6",
            count(if(Day(cas.openedDate) >= 7  and Day(cas.openedDate) < 8, 1 , null)) as "7",
            count(if(Day(cas.openedDate) >= 8  and Day(cas.openedDate) < 9, 1 , null)) as "8",
            count(if(Day(cas.openedDate) >= 9  and Day(cas.openedDate) < 10, 1 , null)) as "9",
            count(if(Day(cas.openedDate) >= 10 and Day(cas.openedDate) < 11, 1 , null)) as "10",
            count(if(Day(cas.openedDate) >= 11 and Day(cas.openedDate) < 12, 1 , null)) as "11",
            count(if(Day(cas.openedDate) >= 12 and Day(cas.openedDate) < 13, 1 , null)) as "12",
            count(if(Day(cas.openedDate) >= 13 and Day(cas.openedDate) < 14, 1 , null)) as "13",
            count(if(Day(cas.openedDate) >= 14 and Day(cas.openedDate) < 15, 1 , null)) as "14",
            count(if(Day(cas.openedDate) >= 15 and Day(cas.openedDate) < 16, 1 , null)) as "15",
            count(if(Day(cas.openedDate) >= 16 and Day(cas.openedDate) < 17, 1 , null)) as "16",
            count(if(Day(cas.openedDate) >= 17 and Day(cas.openedDate) < 18, 1 , null)) as "17",
            count(if(Day(cas.openedDate) >= 18 and Day(cas.openedDate) < 19, 1 , null)) as "18",
            count(if(Day(cas.openedDate) >= 19 and Day(cas.openedDate) < 20, 1 , null)) as "19",
            count(if(Day(cas.openedDate) >= 20 and Day(cas.openedDate) < 21, 1 , null)) as "20",
            count(if(Day(cas.openedDate) >= 21 and Day(cas.openedDate) < 22, 1 , null)) as "21",
            count(if(Day(cas.openedDate) >= 22 and Day(cas.openedDate) < 23, 1 , null)) as "22",
            count(if(Day(cas.openedDate) >= 23 and Day(cas.openedDate) < 24, 1 , null)) as "23",
            count(if(Day(cas.openedDate) >= 24 and Day(cas.openedDate) < 25, 1 , null)) as "24",
            count(if(Day(cas.openedDate) >= 25 and Day(cas.openedDate) < 26, 1 , null)) as "25",
            count(if(Day(cas.openedDate) >= 26 and Day(cas.openedDate) < 27, 1 , null)) as "26",
            count(if(Day(cas.openedDate) >= 27 and Day(cas.openedDate) < 28, 1 , null)) as "27",
            count(if(Day(cas.openedDate) >= 28 and Day(cas.openedDate) < 29, 1 , null)) as "28",
            count(if(Day(cas.openedDate) >= 29 and Day(cas.openedDate) < 30, 1 , null)) as "29",
            count(if(Day(cas.openedDate) >= 30 and Day(cas.openedDate) < 31, 1 , null)) as "30",
            count(if(Day(cas.openedDate) = 31, 1 , null)) as "31", 
            count(cas.id)                                   as total
    from 
        zt_case as cas
    left join
          zt_user as usr
    on
              usr.account = cas.openedBy
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=cas.project
    LEFT JOIN
        zt_project as pject 
    ON
        SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    where 
        cas.deleted = '0'
    and
        cas.fromCaseId = 0
    {
    '''AND
        cas.openedDate >= "%s" ''' % _date_create_start if _date_create_start else ""
    }
    {
    '''AND
        cas.openedDate < "%s" ''' % _date_create_end if _date_create_end else "" 
    }
    {
    '''AND
        usr.realname = '%s' # Bug关闭人''' % _creator_name[0] if _creator_name and len(_creator_name) == 1
    else '''
       AND
        usr.realname in %s ''' % str(_creator_name) if _creator_name and len(_creator_name) > 1
    else ''
    }    
     group by 
        cas.openedBy, pject.id
    order by
        cas.openedBy
    desc
"""



testcase_requirement_name_list_sql = """
SELECT
		sty.id      as id,
		sty.title   as name
FROM
		zentao.zt_story as sty
WHERE
		sty.deleted='0'
"""



# 查询所有的测试单
def construct_sql_case_test_sheet_by_project_search_sql(
        _project_id=None,
        _subsys_id=None
):
    return f"""
# 查詢所有的测试单
	SELECT
			tsk.id as task_id,
			tsk.`name` as task_name
	FROM
			zt_testtask as tsk 
	LEFT JOIN
			zt_project as pj # 子系统
	ON
			pj.id = tsk.project
	LEFT JOIN
			zt_project as pject # 项目
	ON
			pject.id = SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path, ',', 2), ',', -1)
	WHERE
			tsk.deleted='0'
{
    f'and pject.id={_project_id}' if _project_id 
    else ''
}
{
    f'and pj.id={_subsys_id}' if _subsys_id 
    else ''
}
"""


# 查询所有的子系统
def construct_sql_case_subsys_by_project_search_sql(_project_id=None):
    return f"""
select 
        proj.id as id, proj.name as name
from
        zt_project as proj   
where
        proj.type='project'
and
        proj.parent > 0
and
        proj.deleted = '0'
{
    f'and proj.parent={_project_id}' if _project_id 
    else ''
}
"""

# 查询所有的迭代名称
def construct_sql_case_iteraion_by_subsys_search_sql(_subsys_id=None):
    return f"""
select 
        proj.id as id, proj.name as name
from
        zt_project as proj
where
        proj.type='sprint'
and
        proj.parent > 0
and
        proj.deleted = '0'
{
    f'and proj.parent={_subsys_id}' if _subsys_id 
    else ''
}
"""


# 查询所有的版本
def construct_sql_case_iteraion_by_iteraion_search_sql(_iteration_id=None):
    return f"""
select 
        bld.id as id, bld.name as name
from
        zt_build as bld
left join
        zt_project as iter
on 
        bld.execution = iter.id
where
        iter.type='sprint'
and
        bld.deleted = '0'
{
    f'and iter.id={_iteration_id}' if _iteration_id 
    else ''
}
"""


testcase_executor_name_list_sql = """
select 
		usr.realname as name
FROM
		zt_case as cas 
LEFT JOIN
		zt_user as usr 
ON
		usr.account=cas.lastRunner
WHERE
		cas.lastRunner is not NULL
AND
		usr.realname != 'admin'
group by 
		cas.lastRunner 

;"""



def construct_sql_testcase_execute_data_distribute_by_date_with_all(
    _date_execute_start,
    _date_execute_end,
    _executor_name,
    _case_type
):
    return f"""
select
    "【总数】" as executor,
    count(if(Day(trun.lastRunDate) >= 1  and Day(trun.lastRunDate) < 2, 1 , null)) as "1",
    count(if(Day(trun.lastRunDate) >= 2  and Day(trun.lastRunDate) < 3, 1 , null)) as "2",
    count(if(Day(trun.lastRunDate) >= 3  and Day(trun.lastRunDate) < 4, 1 , null)) as "3",
    count(if(Day(trun.lastRunDate) >= 4  and Day(trun.lastRunDate) < 5, 1 , null)) as "4",
    count(if(Day(trun.lastRunDate) >= 5  and Day(trun.lastRunDate) < 6, 1 , null)) as "5",
    count(if(Day(trun.lastRunDate) >= 6  and Day(trun.lastRunDate) < 7, 1 , null)) as "6",
    count(if(Day(trun.lastRunDate) >= 7  and Day(trun.lastRunDate) < 8, 1 , null)) as "7",
    count(if(Day(trun.lastRunDate) >= 8  and Day(trun.lastRunDate) < 9, 1 , null)) as "8",
    count(if(Day(trun.lastRunDate) >= 9  and Day(trun.lastRunDate) < 10, 1 , null)) as "9",
    count(if(Day(trun.lastRunDate) >= 10 and Day(trun.lastRunDate) < 11, 1 , null)) as "10",
    count(if(Day(trun.lastRunDate) >= 11 and Day(trun.lastRunDate) < 12, 1 , null)) as "11",
    count(if(Day(trun.lastRunDate) >= 12 and Day(trun.lastRunDate) < 13, 1 , null)) as "12",
    count(if(Day(trun.lastRunDate) >= 13 and Day(trun.lastRunDate) < 14, 1 , null)) as "13",
    count(if(Day(trun.lastRunDate) >= 14 and Day(trun.lastRunDate) < 15, 1 , null)) as "14",
    count(if(Day(trun.lastRunDate) >= 15 and Day(trun.lastRunDate) < 16, 1 , null)) as "15",
    count(if(Day(trun.lastRunDate) >= 16 and Day(trun.lastRunDate) < 17, 1 , null)) as "16",
    count(if(Day(trun.lastRunDate) >= 17 and Day(trun.lastRunDate) < 18, 1 , null)) as "17",
    count(if(Day(trun.lastRunDate) >= 18 and Day(trun.lastRunDate) < 19, 1 , null)) as "18",
    count(if(Day(trun.lastRunDate) >= 19 and Day(trun.lastRunDate) < 20, 1 , null)) as "19",
    count(if(Day(trun.lastRunDate) >= 20 and Day(trun.lastRunDate) < 21, 1 , null)) as "20",
    count(if(Day(trun.lastRunDate) >= 21 and Day(trun.lastRunDate) < 22, 1 , null)) as "21",
    count(if(Day(trun.lastRunDate) >= 22 and Day(trun.lastRunDate) < 23, 1 , null)) as "22",
    count(if(Day(trun.lastRunDate) >= 23 and Day(trun.lastRunDate) < 24, 1 , null)) as "23",
    count(if(Day(trun.lastRunDate) >= 24 and Day(trun.lastRunDate) < 25, 1 , null)) as "24",
    count(if(Day(trun.lastRunDate) >= 25 and Day(trun.lastRunDate) < 26, 1 , null)) as "25",
    count(if(Day(trun.lastRunDate) >= 26 and Day(trun.lastRunDate) < 27, 1 , null)) as "26",
    count(if(Day(trun.lastRunDate) >= 27 and Day(trun.lastRunDate) < 28, 1 , null)) as "27",
    count(if(Day(trun.lastRunDate) >= 28 and Day(trun.lastRunDate) < 29, 1 , null)) as "28",
    count(if(Day(trun.lastRunDate) >= 29 and Day(trun.lastRunDate) < 30, 1 , null)) as "29",
    count(if(Day(trun.lastRunDate) >= 30 and Day(trun.lastRunDate) < 31, 1 , null)) as "30",
    count(if(Day(trun.lastRunDate) = 31, 1 , null)) as "31",
    count(1) as total

from
    zt_testresult as trest
left join
    zt_testrun as trun
on
    trun.id = trest.run
left join
    zt_case as cas
on
    cas.id=trun.`case`
LEFT JOIN
    zt_testtask as tsk 
on 
    tsk.id=trun.task 
LEFT JOIN
    zt_project as pj 
ON
    pj.id=tsk.project
LEFT JOIN
    zt_project as pject 
ON
    SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
left join
    zt_user as usr
on
    usr.account = trun.lastRunner
where
    trest.run > 0
and
    cas.deleted = '0'
# and
#     pject.status != "closed"
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
    {
    '''AND
        cas.type = "%s" ''' % _case_type if _case_type else "" 
    }
    {
    '''AND
        usr.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
    }
"""


def construct_sql_testcase_execute_data_distribute_by_date_with_each(
    _date_execute_start,
    _date_execute_end,
    _executor_name,
    _case_type
):
    return f"""
select
    usr.realname as executor,
    count(if(Day(trun.lastRunDate) >= 1  and Day(trun.lastRunDate) < 2, 1 , null)) as "1",
    count(if(Day(trun.lastRunDate) >= 2  and Day(trun.lastRunDate) < 3, 1 , null)) as "2",
    count(if(Day(trun.lastRunDate) >= 3  and Day(trun.lastRunDate) < 4, 1 , null)) as "3",
    count(if(Day(trun.lastRunDate) >= 4  and Day(trun.lastRunDate) < 5, 1 , null)) as "4",
    count(if(Day(trun.lastRunDate) >= 5  and Day(trun.lastRunDate) < 6, 1 , null)) as "5",
    count(if(Day(trun.lastRunDate) >= 6  and Day(trun.lastRunDate) < 7, 1 , null)) as "6",
    count(if(Day(trun.lastRunDate) >= 7  and Day(trun.lastRunDate) < 8, 1 , null)) as "7",
    count(if(Day(trun.lastRunDate) >= 8  and Day(trun.lastRunDate) < 9, 1 , null)) as "8",
    count(if(Day(trun.lastRunDate) >= 9  and Day(trun.lastRunDate) < 10, 1 , null)) as "9",
    count(if(Day(trun.lastRunDate) >= 10 and Day(trun.lastRunDate) < 11, 1 , null)) as "10",
    count(if(Day(trun.lastRunDate) >= 11 and Day(trun.lastRunDate) < 12, 1 , null)) as "11",
    count(if(Day(trun.lastRunDate) >= 12 and Day(trun.lastRunDate) < 13, 1 , null)) as "12",
    count(if(Day(trun.lastRunDate) >= 13 and Day(trun.lastRunDate) < 14, 1 , null)) as "13",
    count(if(Day(trun.lastRunDate) >= 14 and Day(trun.lastRunDate) < 15, 1 , null)) as "14",
    count(if(Day(trun.lastRunDate) >= 15 and Day(trun.lastRunDate) < 16, 1 , null)) as "15",
    count(if(Day(trun.lastRunDate) >= 16 and Day(trun.lastRunDate) < 17, 1 , null)) as "16",
    count(if(Day(trun.lastRunDate) >= 17 and Day(trun.lastRunDate) < 18, 1 , null)) as "17",
    count(if(Day(trun.lastRunDate) >= 18 and Day(trun.lastRunDate) < 19, 1 , null)) as "18",
    count(if(Day(trun.lastRunDate) >= 19 and Day(trun.lastRunDate) < 20, 1 , null)) as "19",
    count(if(Day(trun.lastRunDate) >= 20 and Day(trun.lastRunDate) < 21, 1 , null)) as "20",
    count(if(Day(trun.lastRunDate) >= 21 and Day(trun.lastRunDate) < 22, 1 , null)) as "21",
    count(if(Day(trun.lastRunDate) >= 22 and Day(trun.lastRunDate) < 23, 1 , null)) as "22",
    count(if(Day(trun.lastRunDate) >= 23 and Day(trun.lastRunDate) < 24, 1 , null)) as "23",
    count(if(Day(trun.lastRunDate) >= 24 and Day(trun.lastRunDate) < 25, 1 , null)) as "24",
    count(if(Day(trun.lastRunDate) >= 25 and Day(trun.lastRunDate) < 26, 1 , null)) as "25",
    count(if(Day(trun.lastRunDate) >= 26 and Day(trun.lastRunDate) < 27, 1 , null)) as "26",
    count(if(Day(trun.lastRunDate) >= 27 and Day(trun.lastRunDate) < 28, 1 , null)) as "27",
    count(if(Day(trun.lastRunDate) >= 28 and Day(trun.lastRunDate) < 29, 1 , null)) as "28",
    count(if(Day(trun.lastRunDate) >= 29 and Day(trun.lastRunDate) < 30, 1 , null)) as "29",
    count(if(Day(trun.lastRunDate) >= 30 and Day(trun.lastRunDate) < 31, 1 , null)) as "30",
    count(if(Day(trun.lastRunDate) = 31, 1 , null)) as "31",
    count(1) as total

from
    zt_testresult as trest
left join
    zt_testrun as trun
on
    trun.id = trest.run
left join
    zt_case as cas
on
    cas.id=trun.`case`
LEFT JOIN
    zt_testtask as tsk 
on 
    tsk.id=trun.task 
LEFT JOIN
    zt_project as pj 
ON
    pj.id=tsk.project
LEFT JOIN
    zt_project as pject 
ON
    SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
left join
    zt_user as usr
on
    usr.account = trun.lastRunner
where
    trest.run > 0
and
    cas.deleted = '0'
# and
#     pject.status != 'closed'
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
    {
    '''AND
        cas.type = "%s" ''' % _case_type if _case_type else "" 
    }
    {
    '''AND
        usr.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
    }
group by
    trun.lastRunner
order by
        total
    desc
"""


def construct_sql_testcase_execute_data_distribute_by_time_slot_with_all(
        _date_execute_start,
        _date_execute_end,
        _executor_name,
        _case_type
):
    return f"""
    select
        "【总数】" as executor,
        count(if(TIME(trun.lastRunDate) >='8:00'  and TIME(trun.lastRunDate) <  '9:00', 1, null)) as '+8',
        count(if(TIME(trun.lastRunDate) >='9 :00' and TIME(trun.lastRunDate) < '10:00', 1, null)) as '+9',
        count(if(TIME(trun.lastRunDate) >='10:00' and TIME(trun.lastRunDate) < '11:00', 1, null)) as '+10',
        count(if(TIME(trun.lastRunDate) >='11:00' and TIME(trun.lastRunDate) < '12:00', 1, null)) as '+11',
        count(if(TIME(trun.lastRunDate) >='12:00' and TIME(trun.lastRunDate) < '13:30', 1, null)) as '+12',
        count(if(TIME(trun.lastRunDate) >='13:30' and TIME(trun.lastRunDate) < '14:30', 1, null)) as '+13',
        count(if(TIME(trun.lastRunDate) >='14:30' and TIME(trun.lastRunDate) < '15:30', 1, null)) as '+14',
        count(if(TIME(trun.lastRunDate) >='15:30' and TIME(trun.lastRunDate) < '16:30', 1, null)) as '+15',
        count(if(TIME(trun.lastRunDate) >='16:30' and TIME(trun.lastRunDate) < '17:30', 1, null)) as '+16',
        count(if(TIME(trun.lastRunDate) >='17:30' and TIME(trun.lastRunDate) < '18:30', 1, null)) as '+17',
        count(if(TIME(trun.lastRunDate) >='18:30' and TIME(trun.lastRunDate) < '19:30', 1, null)) as '+18',
        count(if(TIME(trun.lastRunDate) >='19:30' and TIME(trun.lastRunDate) < '20:30', 1, null)) as '+19',
        count(if(TIME(trun.lastRunDate) >='20:30' and TIME(trun.lastRunDate) < '21:30', 1, null)) as '+20',
        count(if(TIME(trun.lastRunDate) >='21:30' and TIME(trun.lastRunDate) < '22:30', 1, null)) as '+21',
        count(if(TIME(trun.lastRunDate) >='22:30' and TIME(trun.lastRunDate) <  '8:00', 1, null)) as '+22',
        count(1) as total
    from
        zt_testresult as trest
    left join
        zt_testrun as trun
    on
        trun.id = trest.run
    left join
        zt_case as cas
    on
        cas.id=trun.`case`
    LEFT JOIN
        zt_testtask as tsk 
    on 
        tsk.id=trun.task 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=tsk.project
    LEFT JOIN
        zt_project as pject 
    ON
        SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    left join
        zt_user as usr
    on
        usr.account = trun.lastRunner
    where
        trest.run > 0
    and
        cas.deleted = '0'
    and 
        pject.status != 'closed'    
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
    {
    '''AND
        cas.type = "%s" ''' % _case_type if _case_type else "" 
    }
    {
    '''AND
        usr.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
    }
"""


def construct_sql_testcase_execute_data_distribute_by_time_slot_with_each(
        _date_execute_start,
        _date_execute_end,
        _executor_name,
        _case_type
):
    return f"""
    select
        usr.realname as executor,
        count(if(TIME(trun.lastRunDate) >='8:00'  and TIME(trun.lastRunDate) <  '9:00', 1, null)) as '+8',
        count(if(TIME(trun.lastRunDate) >='9 :00' and TIME(trun.lastRunDate) < '10:00', 1, null)) as '+9',
        count(if(TIME(trun.lastRunDate) >='10:00' and TIME(trun.lastRunDate) < '11:00', 1, null)) as '+10',
        count(if(TIME(trun.lastRunDate) >='11:00' and TIME(trun.lastRunDate) < '12:00', 1, null)) as '+11',
        count(if(TIME(trun.lastRunDate) >='12:00' and TIME(trun.lastRunDate) < '13:30', 1, null)) as '+12',
        count(if(TIME(trun.lastRunDate) >='13:30' and TIME(trun.lastRunDate) < '14:30', 1, null)) as '+13',
        count(if(TIME(trun.lastRunDate) >='14:30' and TIME(trun.lastRunDate) < '15:30', 1, null)) as '+14',
        count(if(TIME(trun.lastRunDate) >='15:30' and TIME(trun.lastRunDate) < '16:30', 1, null)) as '+15',
        count(if(TIME(trun.lastRunDate) >='16:30' and TIME(trun.lastRunDate) < '17:30', 1, null)) as '+16',
        count(if(TIME(trun.lastRunDate) >='17:30' and TIME(trun.lastRunDate) < '18:30', 1, null)) as '+17',
        count(if(TIME(trun.lastRunDate) >='18:30' and TIME(trun.lastRunDate) < '19:30', 1, null)) as '+18',
        count(if(TIME(trun.lastRunDate) >='19:30' and TIME(trun.lastRunDate) < '20:30', 1, null)) as '+19',
        count(if(TIME(trun.lastRunDate) >='20:30' and TIME(trun.lastRunDate) < '21:30', 1, null)) as '+20',
        count(if(TIME(trun.lastRunDate) >='21:30' and TIME(trun.lastRunDate) < '22:30', 1, null)) as '+21',
        count(if(TIME(trun.lastRunDate) >='22:30' and TIME(trun.lastRunDate) <  '8:00', 1, null)) as '+22',
        count(1) as total
    from
        zt_testresult as trest
    left join
        zt_testrun as trun
    on
        trun.id = trest.run
    left join
        zt_case as cas
    on
        cas.id=trun.`case`
    LEFT JOIN
        zt_testtask as tsk 
    on 
        tsk.id=trun.task 
    LEFT JOIN
        zt_project as pj 
    ON
        pj.id=tsk.project
    LEFT JOIN
        zt_project as pject 
    ON
        SUBSTRING_INDEX(SUBSTRING_INDEX(pj.path,',',2),',',-1)=pject.id
    left join
        zt_user as usr
    on
        usr.account = trun.lastRunner
    where
        trest.run > 0
    and
        cas.deleted = '0'
    and 
        pject.status != 'closed'   
    {
    '''AND
        trun.lastRunDate >= "%s" ''' % _date_execute_start if _date_execute_start else ""
    }
    {
    '''AND
        trun.lastRunDate < "%s" ''' % _date_execute_end if _date_execute_end else "" 
    }
    {
    '''AND
        cas.type = "%s" ''' % _case_type if _case_type else "" 
    }
    {
    '''AND
        usr.realname = '%s' # 执行人''' % _executor_name if _executor_name else ''
    }
    group by
        trun.lastRunner
    order by
        total
    desc
"""


def construct_sql_testcase_create_data_distribute_by_date_with_all(
    _date_create_start,
    _date_create_end,
    _creator_name
):
    return f"""
    select
            "【总数】"                                  as creator,
            count(if(Day(cas.openedDate) >= 1  and Day(cas.openedDate) < 2, 1 , null)) as "1",
            count(if(Day(cas.openedDate) >= 2  and Day(cas.openedDate) < 3, 1 , null)) as "2",
            count(if(Day(cas.openedDate) >= 3  and Day(cas.openedDate) < 4, 1 , null)) as "3",
            count(if(Day(cas.openedDate) >= 4  and Day(cas.openedDate) < 5, 1 , null)) as "4",
            count(if(Day(cas.openedDate) >= 5  and Day(cas.openedDate) < 6, 1 , null)) as "5",
            count(if(Day(cas.openedDate) >= 6  and Day(cas.openedDate) < 7, 1 , null)) as "6",
            count(if(Day(cas.openedDate) >= 7  and Day(cas.openedDate) < 8, 1 , null)) as "7",
            count(if(Day(cas.openedDate) >= 8  and Day(cas.openedDate) < 9, 1 , null)) as "8",
            count(if(Day(cas.openedDate) >= 9  and Day(cas.openedDate) < 10, 1 , null)) as "9",
            count(if(Day(cas.openedDate) >= 10 and Day(cas.openedDate) < 11, 1 , null)) as "10",
            count(if(Day(cas.openedDate) >= 11 and Day(cas.openedDate) < 12, 1 , null)) as "11",
            count(if(Day(cas.openedDate) >= 12 and Day(cas.openedDate) < 13, 1 , null)) as "12",
            count(if(Day(cas.openedDate) >= 13 and Day(cas.openedDate) < 14, 1 , null)) as "13",
            count(if(Day(cas.openedDate) >= 14 and Day(cas.openedDate) < 15, 1 , null)) as "14",
            count(if(Day(cas.openedDate) >= 15 and Day(cas.openedDate) < 16, 1 , null)) as "15",
            count(if(Day(cas.openedDate) >= 16 and Day(cas.openedDate) < 17, 1 , null)) as "16",
            count(if(Day(cas.openedDate) >= 17 and Day(cas.openedDate) < 18, 1 , null)) as "17",
            count(if(Day(cas.openedDate) >= 18 and Day(cas.openedDate) < 19, 1 , null)) as "18",
            count(if(Day(cas.openedDate) >= 19 and Day(cas.openedDate) < 20, 1 , null)) as "19",
            count(if(Day(cas.openedDate) >= 20 and Day(cas.openedDate) < 21, 1 , null)) as "20",
            count(if(Day(cas.openedDate) >= 21 and Day(cas.openedDate) < 22, 1 , null)) as "21",
            count(if(Day(cas.openedDate) >= 22 and Day(cas.openedDate) < 23, 1 , null)) as "22",
            count(if(Day(cas.openedDate) >= 23 and Day(cas.openedDate) < 24, 1 , null)) as "23",
            count(if(Day(cas.openedDate) >= 24 and Day(cas.openedDate) < 25, 1 , null)) as "24",
            count(if(Day(cas.openedDate) >= 25 and Day(cas.openedDate) < 26, 1 , null)) as "25",
            count(if(Day(cas.openedDate) >= 26 and Day(cas.openedDate) < 27, 1 , null)) as "26",
            count(if(Day(cas.openedDate) >= 27 and Day(cas.openedDate) < 28, 1 , null)) as "27",
            count(if(Day(cas.openedDate) >= 28 and Day(cas.openedDate) < 29, 1 , null)) as "28",
            count(if(Day(cas.openedDate) >= 29 and Day(cas.openedDate) < 30, 1 , null)) as "29",
            count(if(Day(cas.openedDate) >= 30 and Day(cas.openedDate) < 31, 1 , null)) as "30",
            count(if(Day(cas.openedDate) = 31, 1 , null)) as "31", 
            count(cas.id)                                   as total
    from 
        zt_case as cas
    left join
        zt_user as usr
    on
        usr.account = cas.openedBy
     where 
        cas.deleted = '0'
    {
    '''AND
        cas.openedDate >= "%s" ''' % _date_create_start if _date_create_start else ""
    }
    {
    '''AND  
        cas.openedDate < "%s" ''' % _date_create_end if _date_create_end else "" 
    }
    {
    '''AND
            usr.realname = '%s' # case创建人''' % _creator_name if _creator_name else ''
    }    
"""


def construct_sql_testcase_create_data_distribute_by_date_with_each(
    _date_create_start,
    _date_create_end,
    _creator_name
):
    return f"""
    select
            usr.realname                                    as creator,
            count(if(Day(cas.openedDate) >= 1  and Day(cas.openedDate) < 2, 1 , null)) as "1",
            count(if(Day(cas.openedDate) >= 2  and Day(cas.openedDate) < 3, 1 , null)) as "2",
            count(if(Day(cas.openedDate) >= 3  and Day(cas.openedDate) < 4, 1 , null)) as "3",
            count(if(Day(cas.openedDate) >= 4  and Day(cas.openedDate) < 5, 1 , null)) as "4",
            count(if(Day(cas.openedDate) >= 5  and Day(cas.openedDate) < 6, 1 , null)) as "5",
            count(if(Day(cas.openedDate) >= 6  and Day(cas.openedDate) < 7, 1 , null)) as "6",
            count(if(Day(cas.openedDate) >= 7  and Day(cas.openedDate) < 8, 1 , null)) as "7",
            count(if(Day(cas.openedDate) >= 8  and Day(cas.openedDate) < 9, 1 , null)) as "8",
            count(if(Day(cas.openedDate) >= 9  and Day(cas.openedDate) < 10, 1 , null)) as "9",
            count(if(Day(cas.openedDate) >= 10 and Day(cas.openedDate) < 11, 1 , null)) as "10",
            count(if(Day(cas.openedDate) >= 11 and Day(cas.openedDate) < 12, 1 , null)) as "11",
            count(if(Day(cas.openedDate) >= 12 and Day(cas.openedDate) < 13, 1 , null)) as "12",
            count(if(Day(cas.openedDate) >= 13 and Day(cas.openedDate) < 14, 1 , null)) as "13",
            count(if(Day(cas.openedDate) >= 14 and Day(cas.openedDate) < 15, 1 , null)) as "14",
            count(if(Day(cas.openedDate) >= 15 and Day(cas.openedDate) < 16, 1 , null)) as "15",
            count(if(Day(cas.openedDate) >= 16 and Day(cas.openedDate) < 17, 1 , null)) as "16",
            count(if(Day(cas.openedDate) >= 17 and Day(cas.openedDate) < 18, 1 , null)) as "17",
            count(if(Day(cas.openedDate) >= 18 and Day(cas.openedDate) < 19, 1 , null)) as "18",
            count(if(Day(cas.openedDate) >= 19 and Day(cas.openedDate) < 20, 1 , null)) as "19",
            count(if(Day(cas.openedDate) >= 20 and Day(cas.openedDate) < 21, 1 , null)) as "20",
            count(if(Day(cas.openedDate) >= 21 and Day(cas.openedDate) < 22, 1 , null)) as "21",
            count(if(Day(cas.openedDate) >= 22 and Day(cas.openedDate) < 23, 1 , null)) as "22",
            count(if(Day(cas.openedDate) >= 23 and Day(cas.openedDate) < 24, 1 , null)) as "23",
            count(if(Day(cas.openedDate) >= 24 and Day(cas.openedDate) < 25, 1 , null)) as "24",
            count(if(Day(cas.openedDate) >= 25 and Day(cas.openedDate) < 26, 1 , null)) as "25",
            count(if(Day(cas.openedDate) >= 26 and Day(cas.openedDate) < 27, 1 , null)) as "26",
            count(if(Day(cas.openedDate) >= 27 and Day(cas.openedDate) < 28, 1 , null)) as "27",
            count(if(Day(cas.openedDate) >= 28 and Day(cas.openedDate) < 29, 1 , null)) as "28",
            count(if(Day(cas.openedDate) >= 29 and Day(cas.openedDate) < 30, 1 , null)) as "29",
            count(if(Day(cas.openedDate) >= 30 and Day(cas.openedDate) < 31, 1 , null)) as "30",
            count(if(Day(cas.openedDate) = 31, 1 , null)) as "31", 
            count(cas.id)                                   as total
    from 
        zt_case as cas
    left join
          zt_user as usr
    on
              usr.account = cas.openedBy
     where cas.deleted = '0'
    {
    '''AND
        cas.openedDate >= "%s" ''' % _date_create_start if _date_create_start else ""
    }
    {
    '''AND
        cas.openedDate < "%s" ''' % _date_create_end if _date_create_end else "" 
    }
    {
    '''AND
            usr.realname = '%s' # Bug关闭人''' % _creator_name if _creator_name else ''
    }    
     group by 
        cas.openedBy
    order by
        total
    desc
"""